﻿using System;
using System.Text;

using NORM.Common;
using NORM.DataBase;
using NORM.Entity;

using System.Collections.Generic;
using System.Reflection;
using System.Diagnostics;

namespace NORM.SQLObject
{
    /// <summary>
    /// 对象查询脚本
    /// </summary>
    public partial class OQL : OQLComponent, IDisposable
    {        
        internal int _limit = 0;//限制数
        internal bool _join = false;//是否有联表查询的标识 
        internal string _commandcode = "SELECT";
        //protected List<string> modelnameArray = new List<string>(); //有联表操作时记录表名  
        protected internal List<EntityBase> modelArray = new List<EntityBase>();
        protected internal Stack<TableField> fieldStack = new Stack<TableField>(); //字段堆栈

        private EntityBase modelobject;
        internal EntityBase Modelobject
        {
            get { return modelobject; }
        }

        private object[] _selectfields;
        internal object[] SelectFields
        {
            get { return _selectfields; }
            set { _selectfields = value; }
        }

        private TableField[] _updatefields;
        internal TableField[] UpdateFields
        {
            get { return _updatefields; }
            set { _updatefields = value; }
        }
        
        private Condition[] _conditions;      
        internal Condition[] Conditions
        {
            set { _conditions = value; }
            get { return _conditions; }
        }

        private object[] _orderbies;
        internal object[] Orderbies
        {
            get { return _orderbies; }
            set { _orderbies = value; }
        }

        private OsqlParameter[] _parameters;
        internal OsqlParameter[] Parameters
        {
            set { _parameters = value; }
            get { return _parameters; }
        }

        protected OQL _childoql = null;
        /// <summary>
        /// 子查询OQL
        /// </summary>
        public OQL ChildOql
        {
            get { return _childoql; }
        }

        protected OQL _parentoql = null;
        protected OQL ParentOql
        {
            get { return _parentoql; }
            set { _parentoql = value; }
        }

        private bool _isEnd = false;
        internal bool IsEnd
        {
            get { return _isEnd; }
            set { _isEnd = value; }
        }

        private int _recordcount = 0;
        public int RecordCount
        {
            internal set { _recordcount = value; }
            get { return _recordcount; }
        }

        //private int _totalrecordcount = 0;
        //public int TotalRecordCount
        //{
        //    internal set { _totalrecordcount = value; }
        //    get { return _totalrecordcount; }
        //}

        internal string sql = string.Empty;
        public override string ToString()
        {
            return this.sql;
        }

        private void OnPropertyChanged(EntityBase sender, EntityPropertyArgs e)
        {
            if (e.PropertyArgsType == 1)
            {
                fieldStack.Push(new TableField()
                {
                    Name = e.Field,
                    Type = e.Type,
                    Entity = sender
                });
            }              
        }

        public OQL(EntityBase model)
        {
            this.Key = "OQL";
            this.Guid = "8160ee7c-67e4-4932-8097-01ee845c2f3d";
            modelArray = new List<EntityBase>();
            modelArray.Add(model);
            modelobject = model;            
            modelobject.PropertyOnGetting += new PropertyChangedHandler(OnPropertyChanged);
        }

        public static OQL From(EntityBase model)
        {
            return new OQL(model);
        }

        public static OQL Update(EntityBase model)
        {
            return new OQL(model).Update();
        }

        public static OQL Delete(EntityBase model)
        {
            return new OQL(model).Delete();
        }

        public static OQL Insert(EntityBase model)
        {
            return new OQL(model).Insert();
        }

        OQL Insert()
        {
            this.sql = "INSERT INTO [" + modelobject.TableName + "]";
            this._commandcode = "INSERT";
            return this;
        }

        OQL Update()
        {
            this.sql = "UPDATE [" + modelobject.TableName + "] ";
            this._commandcode = "UPDATE";
            return this;
        }

        OQL Delete()
        {            
            this.sql = "DELETE FROM [" + modelobject.TableName + "] ";
            this._commandcode = "DELETE";
            return this;
        }
        
        public OQL Select(object[] Field = null)
        {
            string _sql = "SELECT ";

            Type _type = modelobject.GetType();

            if (Field != null)
            {
                this._selectfields = Field;
                string field = string.Empty;             
                foreach (object fi in Field)
                {
                    if (!_join)
                    {
                        if (fi.GetType().Equals(typeof(TableField)))
                        {
                            field = ((TableField)fi).Name;
                        }
                        else
                        {
                            field = fi.ToString();
                            if (field.IndexOf(".") > 0)
                                field = field.Substring(field.IndexOf(".") + 1);
                        }

                        if (field.ToUpper().Contains("AS"))
                        {
                            field = field.Replace("as", "AS").Replace("As", "AS").Replace("as", "AS");
                            string asname = field.Substring(field.LastIndexOf("AS") + 2).Trim();
                            string orname = field.Substring(0, field.LastIndexOf("AS")).Trim();
                            _sql += "[" + orname + "]" + " AS [" + asname + "] ,";
                        }
                        else
                        {
                            _sql += "[" + field + "]" + " ,";
                        }
                    }
                    else
                    {
                        field = fi.ToString();

                        string t = string.Empty;
                        if (field.IndexOf(".") > 0)
                            GetAsTableName(field.Substring(0, field.LastIndexOf(".")), out t);
                        else
                            GetAs2TableName(field, out t);
                        string f = field.Substring(field.LastIndexOf(".") + 1);

                        //string t = string.Empty;
                        //GetAs2TableName(field, out t);
                        //string f = field;

                        if (f.ToUpper().Contains("AS"))
                        {
                            f = f.Replace("as", "AS").Replace("As", "AS").Replace("as", "AS");
                            string asname = field.Substring(field.LastIndexOf("AS") + 2).Trim();
                            string orname = f.Substring(0, f.LastIndexOf("AS")).Trim();
                            _sql += ""+t+".[" + orname + "]" + " AS [" + asname + "] ,";
                        }
                        else
                        {
                            _sql += "" + t + ".[" + f + "]" + " ,";
                        }

                    }

                    
                }

                _sql = _sql.TrimEnd(',');
            }
            else
            {
                if (!_join)
                {
                    //PropertyInfo[] PropertiesInfo = _type.GetProperties();
                    PropertyInfo[] PropertiesInfo = CacheProperties.cacheInstance.GetProperties(_type);
                    foreach (PropertyInfo pi in PropertiesInfo)
                    {
                        //if (pi.Name != "TableName" && pi.Name != "Properties")
                        //    _sql += "[" + pi.Name + "]" + " ,";

                        if (pi.Name == "TableName" || pi.Name == "Properties")
                            continue;

                        var attrs = pi.GetCustomAttributes(false);
                        if (attrs != null && attrs.Length > 0)
                        {
                            foreach(var attr in attrs)
                            {
                                if (attr.ToString().StartsWith("Name::"))
                                {
                                    var attrdescrib = attr.ToString();
                                    attrdescrib = attrdescrib.Substring(attrdescrib.IndexOf("Name::") + "Name::".Length);
                                    var columns = attrdescrib.Split(',');
                                    if (columns.Length > 1)
                                    {
                                        _sql += "(";

                                        for (int i = 0; i < columns.Length; i++)
                                        {
                                            string column = columns[i];
                                            if (i == 0)
                                                _sql += "[" + column + "]";
                                            else
                                                _sql += "+[" + column + "]";
                                        }
                                        _sql += ") AS ["+ pi.Name + "]";
                                    }
                                    else
                                    {
                                        _sql += "[" + columns[0] + "]";
                                    }

                                    _sql += " ,";
                                }
                                else
                                {
                                    _sql += "[" + pi.Name + "]" + " ,";
                                }
                            }
                        }
                        else
                        {
                            _sql += "[" + pi.Name + "]" + " ,";
                        }
                    }
                }
                else
                {
                    _sql += " * ";
                }

                _sql = _sql.TrimEnd(',');
            }

            if (!_join)
                _sql += " FROM [" + modelobject.TableName + "] ";

            this.sql = _sql + sql;

            this._commandcode = "SELECT";

            return this;
        }

        public OQL Select(object[] Field, bool Distinct)
        {
            string _sql = "SELECT DISTINCT ";
            
            Type _type = modelobject.GetType();                     

            if (Field != null)
            {
                this._selectfields = Field;
                string field = string.Empty;
                foreach (object fi in Field)
                {
                    field = fi.ToString();
                    if (!_join)
                    {
                        if (field.ToUpper().Contains("AS"))
                        {
                            field = field.Replace("as", "AS").Replace("As", "AS").Replace("as", "AS");
                            string asname = field.Substring(field.LastIndexOf("AS") + 2).Trim();
                            string orname = field.Substring(0, field.LastIndexOf("AS")).Trim();
                            _sql += "[" + orname + "]" + " AS [" + asname + "] ,";
                        }
                        else
                        {
                            _sql += "[" + field + "]" + " ,";
                        }
                    }
                    else
                    {
                        string t = string.Empty;
                        GetAsTableName(field.Substring(0, field.LastIndexOf(".")), out t);
                        string f = field.Substring(field.LastIndexOf(".") + 1);

                        if (f.ToUpper().Contains("AS"))
                        {
                            f = f.Replace("as", "AS").Replace("As", "AS").Replace("as", "AS");
                            string asname = field.Substring(field.LastIndexOf("AS") + 2).Trim();
                            string orname = field.Substring(0, field.LastIndexOf("AS")).Trim();
                            _sql += "" + t + ".[" + orname + "]" + " AS [" + asname + "] ,";
                        }
                        else
                        {
                            _sql += "" + t + ".[" + f + "]" + " ,";
                        }
                    }
                }

                _sql = _sql.TrimEnd(',');
            }
            else
            {
                if (!_join)
                {
                    //PropertyInfo[] PropertiesInfo = _type.GetProperties();
                    PropertyInfo[] PropertiesInfo = CacheProperties.cacheInstance.GetProperties(_type);
                    foreach (PropertyInfo pi in PropertiesInfo)
                    {
                        //if (pi.Name != "TableName" && pi.Name != "Properties")
                        //    _sql += "[" + pi.Name + "]" + " ,";

                        if (pi.Name == "TableName" || pi.Name == "Properties")
                            continue;

                        var attrs = pi.GetCustomAttributes(false);
                        if (attrs != null && attrs.Length > 0)
                        {
                            foreach (var attr in attrs)
                            {
                                if (attr.ToString().StartsWith("Name::"))
                                {
                                    var attrdescrib = attr.ToString();
                                    attrdescrib = attrdescrib.Substring(attrdescrib.IndexOf("Name::") + "Name::".Length);
                                    var columns = attrdescrib.Split(',');
                                    if (columns.Length > 1)
                                    {
                                        _sql += "(";

                                        for (int i = 0; i < columns.Length; i++)
                                        {
                                            string column = columns[i];
                                            if (i == 0)
                                                _sql += "[" + column + "]";
                                            else
                                                _sql += "+[" + column + "]";
                                        }
                                        _sql += ") AS [" + pi.Name + "]";
                                    }
                                    else
                                    {
                                        _sql += "[" + columns[0] + "]";
                                    }

                                    _sql += " ,";
                                }
                                else
                                {
                                    _sql += "[" + pi.Name + "]" + " ,";
                                }
                            }
                        }
                        else
                        {
                            _sql += "[" + pi.Name + "]" + " ,";
                        }

                    }
                }
                else
                {
                    _sql += " * ";
                }

                _sql = _sql.TrimEnd(',');
            }

            if (!_join)
                _sql += " FROM [" + modelobject.TableName + "] ";

            this.sql = _sql + sql;

            this._commandcode = "SELECT";

            return this;
        }

        public OQL Select(Func<object> func)
        {
            lock (Locker.instance)
            {
                var entity = func();

                try
                {
                    if (entity != null)
                    {
                        Type type = entity.GetType();

                        if (type.Name == "String")
                        {
                            string str = entity.ToString();
                            if (str.Contains(","))
                            {
                                return Select(str.Split(','));
                            }
                            else
                            {
                                return Select(new string[] { str });
                            }
                        }
                        else if (type.Name == "String[]")
                        {
                            return Select((String[])entity);
                        }
                        else if (type.Name == "List`1")
                        {
                            if (type.Equals(typeof(List<TableField>)))
                            {
                                return Select(((List<TableField>)entity).ToArray());
                            }
                            else
                            {
                                return Select(((List<object>)entity).ToArray());
                            }
                        }
                        else if (type.Name.StartsWith("<>f__AnonymousType"))
                        {
                            PropertyInfo[] properties = CacheProperties.cacheInstance.GetProperties(type);
                            List<string> list = new List<string>();
                            foreach (PropertyInfo pi in properties)
                                list.Add(fieldStack.Pop().ToString());//list.Add(pi.Name);

                            list.Reverse(); //把顺序倒回来

                            return Select(list.ToArray());
                        }
                        else
                        {
                            //
                        }
                    }

                    this._commandcode = "SELECT";
                }
                catch (Exception ex)
                {
                    throw new NORMException(ExceptionType.OQLExceptin, ex.Message);
                }
            }           

            return this;
        }

        public OQL Where(Condition[] Conditions)
        {
            if (this.sql.Contains("WHERE"))
            {
                this.sql = this.sql.Substring(0, this.sql.LastIndexOf("WHERE"));
            }

            this.sql += "WHERE";

            this._conditions = Conditions;

            List<OsqlParameter> _parameters = new List<OsqlParameter>();

            WhereToParse(this, 0, _parameters);
            
            if (_parameters.Count > 0)
                this._parameters = _parameters.ToArray();

            if (this._commandcode == "UPDATE")
            {
                this.sql = this.sql.Replace("WHERE AND", "WHERE ");
                List<Condition> _conditionarray = new List<Condition>();
                foreach (Condition c in this._conditions)
                {
                    if (!ArrayPlus.ContainsTableFieldKey(this._updatefields, c.Field))
                    {
                        _conditionarray.Add(c);
                    }
                }
                if (_conditionarray != null && _conditionarray.Count > 0)
                    _conditionarray[0].Relation = "";

                this._conditions = _conditionarray.ToArray();

            }

            return this;
        }

        public OQL Where(Func<List<Condition>> func)
        {
            lock (Locker.instance)
            {
                return Where(func().ToArray());
            }            
        }

        public OQL Where(Func<OQLCondition, Boolean> func)
        {
            lock (Locker.instance)
            {
                if (!func(new OQLCondition(this)))
                {
                    throw new NORMException(ExceptionType.OQLExceptin, "构造OQL失败");
                }
                return this;
            }
        }

        public OQL Where(List<Condition> Conditions)
        {
            return Where(Conditions.ToArray());
        }

        public OQL OrderBy(object[] OrderBy)
        {
            if (this.sql.Contains("ORDER BY"))
            {
                this.sql = this.sql.Substring(0, this.sql.LastIndexOf("ORDER BY"));
            }
            this.sql += " ORDER BY ";

            this._orderbies = OrderBy;

            OrderByToParse(this);

            return this;
        }

        public OQL OrderBy(Func<OQLOrder, Boolean> func)
        {
            if (!func(new OQLOrder(this)))
            {
                throw new Exception("Func<OQLOrder, Boolean> 返回为False");
            }
            return this;
        }

        public OQL GroupBy(object[] GroupBy)
        {
            this.sql += " GROUP BY ";

            foreach (object g in GroupBy)
            {
                string gr = g + "";
                this.sql += "[" + gr + "]" + " ,";
            }

            this.sql = sql.TrimEnd(',');

            return this;
        }
        
        public OQL Set(TableField[] Fiels = null)
        {
            StringPlus cmdText = new StringPlus();

            List<OsqlParameter> paramters = new List<OsqlParameter>();
            List<Condition> conditions = new List<Condition>();

            Type type = modelobject.GetType(); //typeof(T);
            //System.Reflection.PropertyInfo[] Properties = type.GetProperties();
            System.Reflection.PropertyInfo[] PropertiesInfo = CacheProperties.cacheInstance.GetProperties(type);

            string _sql = this.sql + "SET "; //"UPDATE [" + modelobject.TableName + "] SET ";

            if (Fiels != null)
            {
                #region 修改指定的字段

                this._updatefields = Fiels;

                for (int i = 0; i < Fiels.Length; i++)
                {
                    TableField tf = Fiels[i] as TableField;
                    if (modelobject.ContainsKeys(tf.Name))
                    {
                        PropertyInfo pi = CacheProperties.cacheInstance.GetProperty(type, tf.Name);
                        if (pi == null)
                            throw new NORMException(ExceptionType.OQLExceptin, "字段:" + tf.Name + "找不到");

                        if (pi.Name != "TableName" && pi.Name != "Properties")
                        {
                            object[] attrs = pi.GetCustomAttributes(false);

                            List<string> customattrs = new List<string>();
                            if (attrs != null)
                            {
                                foreach (object attr in attrs)
                                {
                                    customattrs.Add(attr + "");
                                }
                            }

                            if (attrs != null && attrs.Length > 0)
                            {
                                if (customattrs.Contains("Identify") ||
                           modelobject.PrimaryKey.Contains(pi.Name))
                                {
                                    //不用修改                         
                                }
                                else
                                {
                                    string pi_name = pi.Name;
                                    foreach (var attr in attrs)
                                    {
                                        string attrdescrib = attr.ToString();
                                        if (attrdescrib.StartsWith("Name::"))
                                        {
                                            attrdescrib = attrdescrib.Substring("Name::".Length);
                                            pi_name = attrdescrib.Split(',')[0];
                                        }
                                    }

                                    object value_d = tf.Value;

                                    if (value_d != null)
                                    {
                                        Condition condition = new Condition();
                                        condition.Field = pi_name;
                                        condition.Value = value_d;
                                        condition.Comparison = "=";

                                        _sql += "[" + pi_name + "]=@" + pi_name + " ,";

                                        //兼容老版本ShortDateTime
                                        if (customattrs.Contains("ShortDateTime"))
                                        {
                                            condition.Value = Convert.ToDateTime(value_d).ToString("yyyy-MM-dd");
                                            paramters.Add(new OsqlParameter(pi_name, Convert.ToDateTime(value_d).ToString("yyyy-MM-dd")));
                                        }
                                        //新定义短时间类型 LongDateTime
                                        else if (customattrs.Contains("Type::Ldt"))
                                        {
                                            condition.Value = Convert.ToDateTime(value_d).ToString("yyyy-MM-dd HH:mm:ss.fff");
                                            paramters.Add(new OsqlParameter(pi_name, OsqlParameterDataType.DateTime2, Convert.ToDateTime(value_d).ToString("yyyy-MM-dd HH:mm:ss.fff")));
                                        }
                                        //新定义短时间类型 ShortDateTime
                                        else if (customattrs.Contains("Type::Sdt"))
                                        {
                                            condition.Value = Convert.ToDateTime(value_d).ToString("yyyy-MM-dd");
                                            paramters.Add(new OsqlParameter(pi_name, OsqlParameterDataType.Date, Convert.ToDateTime(value_d).ToString("yyyy-MM-dd")));
                                        }
                                        //布尔类型 Bit
                                        else if (customattrs.Contains("Type::Bit"))
                                        {
                                            condition.Value = new BitString((bool)value_d).ToString();
                                            paramters.Add(new OsqlParameter(pi_name, OsqlParameterDataType.Bit, new BitString((bool)value_d).ToString()));
                                        }
                                        else
                                        {
                                            paramters.Add(new OsqlParameter(pi_name, value_d));
                                        }

                                        conditions.Add(condition);

                                    }
                                    else
                                    {
                                        Condition condition = new Condition();
                                        condition.Field = pi_name;
                                        condition.Value = value_d;
                                        condition.Comparison = "=";

                                        _sql += "[" + pi_name + "]=@" + pi_name + " ,";

                                        //兼容老版本ShortDateTime
                                        if (customattrs.Contains("ShortDateTime"))
                                        {
                                            condition.Value = Convert.ToDateTime(value_d).ToString("yyyy-MM-dd");
                                            paramters.Add(new OsqlParameter(pi_name, OsqlParameterDataType.Date, Convert.ToDateTime(value_d).ToString("yyyy-MM-dd")));
                                        }
                                        //新定义短时间类型 ShortDateTime
                                        else if (customattrs.Contains("Type::Sdt"))
                                        {
                                            condition.Value = Convert.ToDateTime(value_d).ToString("yyyy-MM-dd");
                                            paramters.Add(new OsqlParameter(pi_name, OsqlParameterDataType.Date, Convert.ToDateTime(value_d).ToString("yyyy-MM-dd")));
                                        }
                                        //布尔类型 Bit
                                        else if (customattrs.Contains("Type::Bit"))
                                        {
                                            condition.Value = new BitString((bool)value_d).ToString();
                                            paramters.Add(new OsqlParameter(pi_name, OsqlParameterDataType.Bit, new BitString((bool)value_d).ToString()));
                                        }
                                        else
                                        {
                                            paramters.Add(new OsqlParameter(pi_name, value_d));
                                        }

                                        conditions.Add(condition);
                                    }

                                    tf.Name = pi_name;

                                }
                            }
                            else
                            {

                                object value_d = tf.Value;

                                if (value_d != null)
                                {
                                    Condition condition = new Condition();
                                    condition.Field = pi.Name;
                                    condition.Value = value_d;
                                    condition.Comparison = "=";

                                    _sql += "[" + pi.Name + "]=@" + pi.Name + " ,";

                                    //兼容老版本ShortDateTime
                                    if (customattrs.Contains("ShortDateTime"))
                                    {
                                        condition.Value = Convert.ToDateTime(value_d).ToString("yyyy-MM-dd");
                                        paramters.Add(new OsqlParameter(pi.Name, Convert.ToDateTime(value_d).ToString("yyyy-MM-dd")));
                                    }
                                    //新定义短时间类型 LongDateTime
                                    else if (customattrs.Contains("Type::Ldt"))
                                    {
                                        condition.Value = Convert.ToDateTime(value_d).ToString("yyyy-MM-dd HH:mm:ss.fff");
                                        paramters.Add(new OsqlParameter(pi.Name, OsqlParameterDataType.DateTime2, Convert.ToDateTime(value_d).ToString("yyyy-MM-dd HH:mm:ss.fff")));
                                    }
                                    //新定义短时间类型 ShortDateTime
                                    else if (customattrs.Contains("Type::Sdt"))
                                    {
                                        condition.Value = Convert.ToDateTime(value_d).ToString("yyyy-MM-dd");
                                        paramters.Add(new OsqlParameter(pi.Name, OsqlParameterDataType.Date, Convert.ToDateTime(value_d).ToString("yyyy-MM-dd")));
                                    }
                                    //布尔类型 Bit
                                    else if (customattrs.Contains("Type::Bit"))
                                    {
                                        condition.Value = new BitString((bool)value_d).ToString();
                                        paramters.Add(new OsqlParameter(pi.Name, OsqlParameterDataType.Bit, new BitString((bool)value_d).ToString()));
                                    }
                                    else
                                    {
                                        paramters.Add(new OsqlParameter(pi.Name, value_d));
                                    }

                                    conditions.Add(condition);

                                }
                                else
                                {
                                    Condition condition = new Condition();
                                    condition.Field = pi.Name;
                                    condition.Value = value_d;
                                    condition.Comparison = "=";

                                    _sql += "[" + pi.Name + "]=@" + pi.Name + " ,";

                                    //兼容老版本ShortDateTime
                                    if (customattrs.Contains("ShortDateTime"))
                                    {
                                        condition.Value = Convert.ToDateTime(value_d).ToString("yyyy-MM-dd");
                                        paramters.Add(new OsqlParameter(pi.Name, Convert.ToDateTime(value_d).ToString("yyyy-MM-dd")));
                                    }
                                    //新定义短时间类型 ShortDateTime
                                    else if (customattrs.Contains("Type::Sdt"))
                                    {
                                        condition.Value = Convert.ToDateTime(value_d).ToString("yyyy-MM-dd");
                                        paramters.Add(new OsqlParameter(pi.Name, OsqlParameterDataType.Date, Convert.ToDateTime(value_d).ToString("yyyy-MM-dd")));
                                    }
                                    //布尔类型 Bit
                                    else if (customattrs.Contains("Type::Bit"))
                                    {
                                        condition.Value = new BitString((bool)value_d).ToString();
                                        paramters.Add(new OsqlParameter(pi.Name, OsqlParameterDataType.Bit, new BitString((bool)value_d).ToString()));
                                    }
                                    else
                                    {
                                        paramters.Add(new OsqlParameter(pi.Name, value_d));
                                    }

                                    conditions.Add(condition);
                                }

                            }


                        }
                    }
                }

                _sql = _sql.TrimEnd(',');

                cmdText.Append(_sql);

                #endregion
            }
            else
            {
                #region Fiels 为空时

                List<TableField> _tfs = new List<TableField>();

                foreach (System.Reflection.PropertyInfo pi in PropertiesInfo)
                {
                    if (pi.Name != "TableName" && pi.Name != "Properties")
                    {
                        object[] attrs = pi.GetCustomAttributes(false);

                        List<string> customattrs = new List<string>();

                        if (attrs != null)
                        {
                            foreach (object attr in attrs)
                            {
                                customattrs.Add(attr + "");
                            }
                        }

                        if (attrs != null && attrs.Length > 0)
                        {
                            if (customattrs.Contains("Identify") ||
                           modelobject.PrimaryKey.Contains(pi.Name))
                            {
                                //不用修改                         
                            }
                            else
                            {
                                string pi_name = pi.Name;
                                foreach (var attr in attrs)
                                {
                                    string attrdescrib = attr.ToString();
                                    if (attrdescrib.StartsWith("Name::"))
                                    {
                                        attrdescrib = attrdescrib.Substring("Name::".Length);
                                        pi_name = attrdescrib.Split(',')[0];
                                    }
                                }

                                Type _type = pi.PropertyType;
                                object value_d = pi.GetValue(modelobject, null);
                                if (value_d != null)
                                {
                                    //Condition condition = new Condition();
                                    //condition.Field = pi.Name;
                                    //condition.Value = value_d;
                                    //condition.Comparison = "=";

                                    TableField tf = new TableField();
                                    tf.Name = pi_name;
                                    tf.Entity = modelobject;
                                    tf.Value = value_d;

                                    _sql += "[" + pi_name + "]=@" + pi_name + " ,";

                                    //兼容老版本ShortDateTime
                                    if (customattrs.Contains("ShortDateTime"))
                                    {
                                        //condition.Value = Convert.ToDateTime(value_d).ToString("yyyy-MM-dd");
                                        paramters.Add(new OsqlParameter(pi_name, Convert.ToDateTime(value_d).ToString("yyyy-MM-dd")));
                                    }
                                    //新定义短时间类型 ShortDateTime
                                    else if (customattrs.Contains("Type::Sdt"))
                                    {
                                        //condition.Value = Convert.ToDateTime(value_d).ToString("yyyy-MM-dd");
                                        paramters.Add(new OsqlParameter(pi_name, OsqlParameterDataType.Date, Convert.ToDateTime(value_d).ToString("yyyy-MM-dd")));
                                    }
                                    //布尔类型 Bit
                                    else if (customattrs.Contains("Type::Bit"))
                                    {
                                        //condition.Value = new NpgsqlTypes.BitString((bool)value_d).ToString();
                                        paramters.Add(new OsqlParameter(pi_name, OsqlParameterDataType.Bit, new BitString((bool)value_d).ToString()));
                                    }
                                    else
                                    {
                                        //condition.Value = value_d;
                                        paramters.Add(new OsqlParameter(pi_name, value_d));
                                    }

                                    //conditions.Add(condition);
                                    _tfs.Add(tf);

                                }
                            }
                        }
                        else
                        {
                            Type _type = pi.PropertyType;
                            object value_d = pi.GetValue(modelobject, null);
                            if (value_d != null)
                            {
                                //Condition condition = new Condition();
                                //condition.Field = pi.Name;
                                //condition.Value = value_d;
                                //condition.Comparison = "=";

                                TableField tf = new TableField();
                                tf.Name = pi.Name;
                                tf.Entity = modelobject;
                                tf.Value = value_d;

                                _sql += "[" + pi.Name + "]=@" + pi.Name + " ,";

                                //兼容老版本ShortDateTime
                                if (customattrs.Contains("ShortDateTime"))
                                {
                                    //condition.Value = Convert.ToDateTime(value_d).ToString("yyyy-MM-dd");
                                    paramters.Add(new OsqlParameter(pi.Name, Convert.ToDateTime(value_d).ToString("yyyy-MM-dd")));
                                }
                                //新定义短时间类型 ShortDateTime
                                else if (customattrs.Contains("Type::Sdt"))
                                {
                                    //condition.Value = Convert.ToDateTime(value_d).ToString("yyyy-MM-dd");
                                    paramters.Add(new OsqlParameter(pi.Name, OsqlParameterDataType.Date, Convert.ToDateTime(value_d).ToString("yyyy-MM-dd")));
                                }
                                //布尔类型 Bit
                                else if (customattrs.Contains("Type::Bit"))
                                {
                                    //condition.Value = new NpgsqlTypes.BitString((bool)value_d).ToString();
                                    paramters.Add(new OsqlParameter(pi.Name, OsqlParameterDataType.Bit, new BitString((bool)value_d).ToString()));
                                }
                                else
                                {
                                    //condition.Value = value_d;
                                    paramters.Add(new OsqlParameter(pi.Name, value_d));
                                }

                                //conditions.Add(condition);
                                _tfs.Add(tf);

                            }
                        }

                    }
                }

                _sql = _sql.TrimEnd(',') + " WHERE ";

                foreach (string Key in modelobject.PrimaryKey)
                {
                    _sql += "[" + Key + "]=@" + Key + " AND";
                    paramters.Add(new OsqlParameter(Key, type.GetProperty(Key).GetValue(modelobject, null)));

                    Condition condition = new Condition();
                    condition.Field = Key;
                    condition.Value = type.GetProperty(Key).GetValue(modelobject, null);
                    condition.Comparison = "=";
                    conditions.Add(condition);
                }

                if (_sql.Contains("AND"))
                {
                    _sql = _sql.Substring(0, _sql.LastIndexOf("AND"));
                }

                cmdText.Append(_sql);

                this._updatefields = _tfs.ToArray();

                #endregion
            }

            this.sql = cmdText.Value;
            if (paramters.Count > 0)
                this._parameters = paramters.ToArray();

            this._conditions = conditions.ToArray();

            return this;

        }

        public OQL Set(Func<object> func)
        {
            lock (Locker.instance)
            {
                var entity = func();

                try
                {
                    Type type = entity.GetType();

                    if (type.Name.StartsWith("List"))
                    {
                        List<TableField> tfs = new List<TableField>();

                        foreach (string property in (List<string>)entity)
                        {
                            TableField tf1 = new TableField();

                            string name = string.Empty, value = string.Empty;
                            TableField tf = new TableField();
                            name = property.Substring(0, property.IndexOf("="));
                            value = property.Substring(property.IndexOf("=") + 1);
                            value = value.Trim('\'');

                            tf1.Name = name.Trim();
                            tf1.Entity = modelobject;
                            tf1.Value = value;
                            tfs.Add(tf1);

                        }

                        return Set(tfs.ToArray());
                    }
                    else if (type.Name.StartsWith("<>f__AnonymousType"))
                    {
                        List<TableField> tfs = new List<TableField>();
                        var properties = CacheProperties.cacheInstance.GetProperties(type);

                        foreach (var property in properties)
                        {
                            TableField tf2 = new TableField();
                            tf2.Name = property.Name;
                            tf2.Entity = modelobject;
                            tf2.Value = property.GetValue(entity, null);
                            tfs.Add(tf2);
                        }

                        return Set(tfs.ToArray());
                    }
                }
                catch (Exception ex)
                {
                    throw new NORMException(ExceptionType.OQLExceptin, ex.Message);
                }

                return this;
            }
        }

        public OQL Values(TableField[] Fiels = null)
        {
            this._parameters = null;
            List<OsqlParameter> paramaters = new List<OsqlParameter>();

            if (Fiels != null)
            {               
                string fieldsStr = "";
                string valuesStr = "";

                StringPlus cmdText = new StringPlus();

                //cmdText.Append("INSERT INTO ");
                //cmdText.Append("[" + modelobject.TableName + "] ");

                int index = 0;
                foreach (TableField tf in Fiels)
                {
                    if (index == 0)
                    {
                        fieldsStr += "[" + tf.Name + "]";
                        valuesStr += "@" + tf.Name + "";
                    }
                    else
                    {
                        fieldsStr += ",[" + tf.Name + "]";
                        valuesStr += ",@" + tf.Name + "";
                    }

                    paramaters.Add(new OsqlParameter(tf.Name, tf.Value));

                    index++;
                }

                cmdText.Append("(" + fieldsStr + ") ");
                cmdText.Append("VALUES(" + valuesStr + ")");

                this.sql += cmdText.Value;                
            }
            else
            {
                string feildsStr = string.Empty;
                string valuesStr = string.Empty;

                StringPlus cmdText = new StringPlus();

                //cmdText.Append("INSERT INTO ");
                //cmdText.Append("[" + modelobject.TableName + "] ");

                Type type = modelobject.GetType(); //typeof(T);
                //System.Reflection.PropertyInfo[] PropertiesInfo = type.GetProperties();
                System.Reflection.PropertyInfo[] PropertiesInfo = CacheProperties.cacheInstance.GetProperties(type);

                cmdText.Append("( ");

                foreach (System.Reflection.PropertyInfo pi in PropertiesInfo)
                {
                    object o = pi.GetValue(modelobject, null);
                    if (pi.Name != "TableName" && pi.Name != "Properties")
                    {

                        if (o != null && !DBNull.Value.Equals(o)
                            && !o.ToString().Equals("0001-01-01 0:00:00")
                            && !DataValue.GuidEmpty.Equals(o))
                        {
                            var attrs = pi.GetCustomAttributes(false);

                            if (attrs != null && attrs.Length > 0)
                            {
                                List<string> attrs_list = ArrayPlus.ToStringList(attrs);

                                if (attrs_list.Contains("Identify"))
                                {
                                    //自增列
                                }
                                else
                                {
                                    string pi_Name = pi.Name;

                                    foreach (var attr in attrs)
                                    {
                                        if (attr.ToString().StartsWith("Name::"))
                                        {
                                            pi_Name = attr.ToString();
                                            pi_Name = pi_Name.Substring(pi_Name.IndexOf("Name::") + "Name::".Length);
                                            pi_Name = pi_Name.Split(',')[0];
                                        }
                                    }

                                    feildsStr += "[" + pi_Name + "]" + " ,";
                                }
                            }
                            else
                            {
                                feildsStr += "[" + pi.Name + "]" + " ,";
                            }
                        }

                    }
                }
                feildsStr = feildsStr.TrimEnd(',');
                cmdText.Append(feildsStr);
                cmdText.Append(") ");

                cmdText.Append("VALUES( ");
              
                foreach (System.Reflection.PropertyInfo pi in PropertiesInfo)
                {
                    object o = pi.GetValue(modelobject, null);
                    if (pi.Name != "TableName" && pi.Name != "Properties")
                    {
                        if (o != null && !DBNull.Value.Equals(o)
                            && !o.ToString().Equals("0001-01-01 0:00:00")
                            && !DataValue.GuidEmpty.Equals(o))
                        {
                            object[] attrs = pi.GetCustomAttributes(false);
                            if (attrs != null && attrs.Length > 0)
                            {
                                List<string> attrs_list = ArrayPlus.ToStringList(attrs);
                                if (attrs_list.Contains("Identify"))
                                {
                                    //自增列
                                }
                                else
                                {
                                    string pi_Name = pi.Name;
                                    foreach (var attr in attrs)
                                    {
                                        if (attr.ToString().StartsWith("Name::"))
                                        {
                                            pi_Name = attr.ToString();
                                            pi_Name = pi_Name.Substring(pi_Name.IndexOf("Name::") + "Name::".Length);
                                            pi_Name = pi_Name.Split(',')[0];
                                        }
                                    }

                                    if (attrs_list.Contains("ShortDateTime"))
                                    {
                                        valuesStr += "@" + pi_Name + " ,";
                                        paramaters.Add(new OsqlParameter(pi_Name, OsqlParameterDataType.Date, Convert.ToDateTime(o).ToString("yyyy-MM-dd")));
                                    }
                                    else if (attrs_list.Contains("Type::Ldt"))
                                    {
                                        valuesStr += "@" + pi_Name + " ,";
                                        paramaters.Add(new OsqlParameter(pi_Name, OsqlParameterDataType.DateTime2, Convert.ToDateTime(o).ToString("yyyy-MM-dd HH:mm:ss.fff")));
                                    }
                                    //新定义短时间类型 ShortDateTime
                                    else if (attrs_list.Contains("Type::Sdt"))
                                    {
                                        valuesStr += "@" + pi_Name + " ,";
                                        paramaters.Add(new OsqlParameter(pi_Name, OsqlParameterDataType.Date, Convert.ToDateTime(o).ToString("yyyy-MM-dd")));
                                    }
                                    else if (attrs_list.Contains("Type::Bit"))
                                    {
                                        valuesStr += "@" + pi_Name + " ,";
                                        paramaters.Add(new OsqlParameter(pi_Name, OsqlParameterDataType.Bit, new BitString((bool)o).ToString()));
                                    }
                                    else if (attrs_list.Contains("Type::Box"))
                                    {
                                        valuesStr += "@" + pi_Name + " ,";
                                        paramaters.Add(new OsqlParameter(pi_Name, o));
                                    }
                                    else
                                    {
                                        valuesStr += "@" + pi_Name + " ,";
                                        paramaters.Add(new OsqlParameter(pi_Name, o));
                                    }
                                }
                            }
                            else
                            {
                                valuesStr += "@" + pi.Name + " ,";
                                paramaters.Add(new OsqlParameter(pi.Name, o));
                            }
                        }
                    }
                }

                valuesStr = valuesStr.TrimEnd(',');
                cmdText.Append(valuesStr);
                cmdText.Append(")");

                this.sql += cmdText.Value;             
            }

            if (paramaters.Count > 0)
                this._parameters = paramaters.ToArray();

            return this;
        }

        public OQL Values(Func<object> func)
        {
            lock (Locker.instance)
            {
                var entity = func();

                try
                {
                    Type type = entity.GetType();

                    if (type.Name.StartsWith("List"))
                    {
                        List<TableField> tfs = new List<TableField>();

                        foreach (string property in (List<string>)entity)
                        {
                            TableField tf1 = new TableField();

                            string name = string.Empty, value = string.Empty;
                            TableField tf = new TableField();
                            name = property.Substring(0, property.IndexOf("="));
                            value = property.Substring(property.IndexOf("=") + 1);
                            value = value.Trim('\'');

                            tf1.Name = name.Trim();
                            tf1.Entity = modelobject;
                            tf1.Value = value;
                            tfs.Add(tf1);

                        }

                        return Values(tfs.ToArray());
                    }
                    else if (type.Name.StartsWith("<>f__AnonymousType"))
                    {
                        List<TableField> tfs = new List<TableField>();
                        var properties = CacheProperties.cacheInstance.GetProperties(type);

                        foreach (var property in properties)
                        {
                            TableField tf2 = new TableField();
                            tf2.Name = property.Name;
                            tf2.Entity = modelobject;
                            tf2.Value = property.GetValue(entity, null);
                            tfs.Add(tf2);
                        }

                        return Values(tfs.ToArray());
                    }

                    return this;
                }
                catch (Exception ex)
                {
                    throw new NORMException(ExceptionType.OQLExceptin, ex.Message);
                }
            }
        }
        
        public OQL Limit(int Size)
        {
            this._limit = Size;
            this.sql += " Limit " + Size + "";
            return this;
        }

        public OQL End
        {
            get
            {                  
                this.sql = this.sql.TrimEnd(';') + ";";
                this._isEnd = true;
                Dispose();
      
                return this;
            }
        }

        public void Dispose()
        {
            foreach (var model in modelArray)
                model.PropertyOnGetting -= new PropertyChangedHandler(OnPropertyChanged);
            fieldStack.Clear();
        }

    }

}
